package com.jiepos.mpos.biz.transaction.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONObject;
import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.constant.SystemConstant;
import com.jiepos.mpos.common.dao.LogStlMchntSumDao;
import com.jiepos.mpos.common.entity.App_Log_Stl_Mchnt_SumEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.DateUtil;
import com.jiepos.mpos.core.util.ExcelUtil;
import com.jiepos.mpos.core.util.StringUtils;

/**
 * 会员清算交易汇总业务类
 * 
 * @ProjectName: [mpos1]
 * @Package com.jiepos.mpos.biz.transaction.service
 * @author 肖志伟
 * @version 1.0
 * @since 2017年10月10日 下午2:06:29
 * @history（历次修订内容、修订人、修订时间等） @UpdateRemark [说明本次修改内容]
 * @Copyright (c) 2017 江苏杰博实信息技术有限公司
 */
@Service
public class LogStlMchntSumService extends BaseService {

	@Autowired
	private LogStlMchntSumDao logStlMchntSumDao;

	/**
	 * 列表页数据查询
	 * 
	 * @param request
	 * @param pagination
	 * @param logStlMchntSumEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_no
	 * @return
	 */
	@SuppressWarnings({"rawtypes"})
	public Map getTransactionLogList(HttpServletRequest request, Pagination pagination,
			App_Log_Stl_Mchnt_SumEntity logStlMchntSumEntity, String stl_dt_start, String stl_dt_end,
			String custom_name) {
		JSONObject common = getAllTransLogSqlParams(logStlMchntSumEntity, stl_dt_start, stl_dt_end, custom_name, null);
		pagination.setQueryString(common.getString("sql"));
		pagination.queryString = this.getCustomAuthSql(request, pagination.queryString, "t1", null);
		pagination.queryString = pagination.queryString + " order by t1.stl_dt,t1.amt desc";
		log.debug("getTransactionLogList queryString: " + pagination.queryString);
		return logStlMchntSumDao.getPaginationMap(pagination, "t1", common.getObject("params", List.class));

	}

	/**
	 * 获取汇总数据
	 * 
	 * @param request
	 * @param pagination
	 * @param logStlMchntSumEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_no
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Map getLogStlMchntInfoTotal(HttpServletRequest request, App_Log_Stl_Mchnt_SumEntity logStlMchntSumEntity,
			String stl_dt_start, String stl_dt_end, String custom_name) {
		StringBuffer sqlSb = new StringBuffer();
		sqlSb.append("select ");
		sqlSb.append("to_char(sum(t1.amt),'fm9999999990.00') as all_amt, "); //交易金额
		sqlSb.append("to_char(sum(t1.midfee),'fm9999999990.00') as all_midfee, "); //会员手续费
		sqlSb.append("to_char(sum(t1.ins_stl_fee),'fm9999999990.00') as all_ins_stl_fee, "); // 机构结算手续费
		sqlSb.append("to_char(sum(t1.custom_stl_fee),'fm9999999990.00') as all_custom_stl_fee, "); //会员结算手续费
		/*sqlSb.append("to_char(sum(t1.midfee - t1.custom_stl_fee - t1.ins_stl_fee),'fm9999999990.00') as all_tpm "); //分润毛利*/
		// 交易分润毛利 = (会员手续费-机构结算手续费)- (会员手续费-会员结算手续费)
		sqlSb.append("to_char(sum((t1.midfee - t1.ins_stl_fee)-(t1.midfee - t1.custom_stl_fee)), 'fm9999999990.00') as all_tpm ");
		
		sqlSb.append("from ");
		sqlSb.append(Table.T_APP_LOG_STL_MCHNT_SUM + " t1 ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INS_INF + " t2 ");
		sqlSb.append("on t1.ins_cd = t2.ins_cd ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_CUSTOM_INF + " t3 ");
		sqlSb.append("on t1.custom_no = t3.custom_no ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_INF + " t4 ");
		sqlSb.append("on t1.service_no = t4.service_no ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_PARAM + " t5 ");
		sqlSb.append("on t1.param_no = t5.param_no ");
		sqlSb.append(" where 1=1 ");
		List list = new ArrayList();
		if (!StringUtils.isBlank(stl_dt_start)) {
			Date date = DateUtil.strToDate(stl_dt_start, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt >= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{
			Date yesterday = DateUtil.strToDate(DateUtil.getDecreaseDaysDate(new Date(), 2), DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt >= ? ");
			list.add(DateUtil.dateToStr(yesterday, "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(stl_dt_end)) {
			Date date = DateUtil.strToDate(stl_dt_end, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{
			Date yesterday = DateUtil.strToDate(DateUtil.getDecreaseDaysDate(new Date(), 2), DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(yesterday, "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(logStlMchntSumEntity.getCustom_no())) {
			sqlSb.append(" and t3.custom_no like ? ");
			list.add("%"+logStlMchntSumEntity.getCustom_no()+"%");
		}
		if (!StringUtils.isBlank(custom_name)) {
			sqlSb.append(" and t3.name like ? ");
			list.add("%" + custom_name + "%");
		}
		if (!StringUtils.isBlank(logStlMchntSumEntity.getLogin_id())) {
			sqlSb.append(" and t3.login_id like ? ");
			list.add("%"+logStlMchntSumEntity.getLogin_id()+"%");
		}
		if (!StringUtils.isBlank(logStlMchntSumEntity.getIns_cd())) {
			sqlSb.append(" and t2.ins_cd like ? ");
			list.add("%"+logStlMchntSumEntity.getIns_cd()+"%");
		}
		String sql = sqlSb.toString();
//	/	sql = this.getInsAuthSql(request, sql, "t1", null);
		sql = this.getCustomAuthSql(request, sql, "t1", null);
		log.debug("getTransactionLogList queryString: " + sql);
		return logStlMchntSumDao.queryForMap(sql, list);

	}

	/**
	 * 获取所有渠道数据
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getAllChannel() {
		StringBuffer sqlSb = new StringBuffer();
		sqlSb.append("select t1.channel_no||'-'||t1.chn_nm as chn_nm, t1.channel_no from ");
		sqlSb.append(Table.T_APP_INF_CHANNEL + " t1 ");
		sqlSb.append("where 1=1 ");
		log.debug("getAllChannel sql:" + sqlSb.toString());
		return logStlMchntSumDao.queryForList(sqlSb.toString(), null);
	}

	/**
	 * 导出数据
	 * 
	 * @param fileName
	 * @param request
	 * @param response
	 * @param pagination
	 * @param logStlMchntSumEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_no
	 * @param custom_name
	 * @param custom_phone
	 * @param ins_cd
	 * @param ids
	 *            勾选的数据
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public String executeSaveLogStlMchntSum(String fileName, HttpServletRequest request, HttpServletResponse response,
			Pagination pagination, App_Log_Stl_Mchnt_SumEntity logStlMchntSumEntity, String stl_dt_start,
			String stl_dt_end, String custom_no, String custom_name, String custom_phone, String ins_cd, String ids) {
		JSONObject common = getAllTransLogSqlParams(logStlMchntSumEntity, stl_dt_start, stl_dt_end, custom_name, ids);
		String sql = this.getCustomAuthSql(request, common.getString("sql"), "t1", null);
		sql = sql + " order by t1.stl_dt,t1.amt desc";
		List<Map> resultList = logStlMchntSumDao.queryForList(sql, common.getObject("params", List.class));
		List<String> headList = new ArrayList<String>();
		headList.add("清算日期");
		headList.add("会员编号");
		headList.add("会员名称");
		headList.add("所属机构");
		headList.add("业务名称");
		headList.add("业务参数名称");
		headList.add("交易金额");
		headList.add("交易笔数(笔)");
		headList.add("会员手续费");
		headList.add("会员结算手续费");
		headList.add("机构结算手续费");
		headList.add("交易分润毛利");
		headList.add("交易渠道");
		return ExcelUtil.writeExcel(fileName, "会员清算交易汇总", headList, resultList);
	}

	/**
	 * 会员清算交易汇总导出
	 * 
	 * @param request
	 * @param response
	 * @param pagination
	 * @param logStlMchntSumEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_no
	 * @param custom_name
	 * @param custom_phone
	 * @param ins_cd
	 * @param ids
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public JSONObject getAllTransLogSqlParams(App_Log_Stl_Mchnt_SumEntity logStlMchntSumEntity, String stl_dt_start,
			String stl_dt_end, String custom_name, String ids) {
		StringBuffer sqlSb = new StringBuffer();
		sqlSb.append("select ");
		sqlSb.append("t1.stl_dt as stl_dt1, ");
		/*sqlSb.append("t1.stl_dt, ");*/
		sqlSb.append("TO_CHAR(TO_DATE(t1.stl_dt, 'YYYY-MM-DD'), 'yyyy-mm-dd') as stl_dt, ");
		sqlSb.append("t3.custom_no as custom_no, "); // 会员编号
		sqlSb.append("CONCAT(RPAD (SUBSTR (t3.login_id, 1, 3),LENGTH (t3.login_id) - 4,'*'),SUBSTR (t3.login_id,LENGTH (t3.login_id) - 3,4)) ||'-'|| t3.name as custom_name, "); // 会员名称
		sqlSb.append("t2.ins_cd||'-'||t2.ins_cn_nm as ins, "); // 所属机构
		sqlSb.append("t4.service_no||'-'||t4.service_name as service_name, ");// 业务名称
		sqlSb.append("t5.param_no||'-'||t5.param_name as service_param_name, "); // 业务参数名称
		sqlSb.append("to_char(t1.amt,'fm9999999990.00') as amt, ");// 交易金额
		sqlSb.append("t1.cnt as cnt, "); // 交易笔数
		sqlSb.append("to_char(t1.midfee,'fm9999999990.00') as midfee, "); // 会员手续费
		sqlSb.append("to_char(t1.custom_stl_fee,'fm9999999990.00') as custom_stl_fee, "); // 会员结算手续费
		sqlSb.append("to_char(t1.ins_stl_fee,'fm9999999990.00') as ins_stl_fee, "); // 机构结算手续费
		// 交易分润毛利 = 会员手续费-会员结算手续费-机构结算手续费
		/*sqlSb.append("to_char(t1.midfee - t1.custom_stl_fee - t1.ins_stl_fee,'fm9999999990.00') as profit_money ");*/
		// 交易分润毛利 = (会员手续费-机构结算手续费)- (会员手续费-会员结算手续费)
		sqlSb.append("to_char(((t1.midfee - t1.ins_stl_fee) - (t1.midfee - t1.custom_stl_fee)), 'fm9999999990.00') as profit_money, ");
		
		sqlSb.append(" t1.channel_no || '-' || t1.chn_nm as channel_no ");
		sqlSb.append("from ");
		sqlSb.append(Table.T_APP_LOG_STL_MCHNT_SUM + " t1 ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INS_INF + " t2 ");
		sqlSb.append("on t1.ins_cd = t2.ins_cd ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_CUSTOM_INF + " t3 ");
		sqlSb.append("on t1.custom_no = t3.custom_no ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_INF + " t4 ");
		sqlSb.append("on t1.service_no = t4.service_no ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_PARAM + " t5 ");
		sqlSb.append("on t1.param_no = t5.param_no ");
		sqlSb.append(" where 1=1 ");
		List list = new ArrayList();
		if (!StringUtils.isBlank(stl_dt_start)) {
			Date date = DateUtil.strToDate(stl_dt_start, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt >= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{
			Date yesterday = DateUtil.strToDate(DateUtil.getDecreaseDaysDate(new Date(), 2), DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt >= ? ");
			list.add(DateUtil.dateToStr(yesterday, "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(stl_dt_end)) {
			Date date = DateUtil.strToDate(stl_dt_end, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{
			Date yesterday = DateUtil.strToDate(DateUtil.getDecreaseDaysDate(new Date(), 2), DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(yesterday, "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(logStlMchntSumEntity.getCustom_no())) {
			sqlSb.append(" and t3.custom_no = ? ");
			list.add(logStlMchntSumEntity.getCustom_no());
		}
		if (!StringUtils.isBlank(custom_name)) {
			sqlSb.append(" and t3.name like ? ");
			list.add("%" + custom_name + "%");
		}
		if (!StringUtils.isBlank(logStlMchntSumEntity.getLogin_id())) {
			sqlSb.append(" and t3.login_id = ? ");
			list.add(logStlMchntSumEntity.getLogin_id());
		}
		if (!StringUtils.isBlank(logStlMchntSumEntity.getIns_cd())) {
			sqlSb.append(" and t2.ins_cd = ? ");
			list.add(logStlMchntSumEntity.getIns_cd());
		}
		//交易渠道
		if (!StringUtils.isBlank(logStlMchntSumEntity.getChannel_no())) {
			sqlSb.append(" and t1.channel_no = ? ");
			list.add(logStlMchntSumEntity.getChannel_no());
		}
		JSONObject common = new JSONObject();
		common.put("sql", sqlSb.toString());
		common.put("params", list);
		return common;
	}

	/**
	 * 获取机构权限拼装sql
	 * 
	 * @param request
	 * @param sq1
	 * @param prefix
	 * @return
	 */
	public String getInsAuthSql(HttpServletRequest request, String sql, String prefix, Map map) {
		if (!StringUtils.isBlank(prefix)) {
			prefix = prefix + ".";
		} else {
			prefix = "";
		}
		String and = "and";
		if (map != null) {
			and = StringUtils.objToString(String.valueOf(map.get("and")));
		}
		StringBuilder sqlBuilder = new StringBuilder();
		// 获取登陆用户名
		String loginName = String.valueOf(request.getSession().getAttribute(SystemConstant.SESSION_LOGIN_USER));

		String ins_uuid = String.valueOf(request.getSession().getAttribute(SystemConstant.SESSION_LOGIN_INS_UUID));

		String ins_phone = String.valueOf(request.getSession().getAttribute(SystemConstant.SESSION_LOGIN_INS_PHONE));
		// 如果是机构信息，则查询机构表
		// 如果此用户是属于某个机构，则获取当前机构及下级机构代码
		sqlBuilder.append(" " + and + " " + prefix + "ins_cd in ");
		sqlBuilder.append(" (select tt.ins_cd from " + Table.T_APP_INS_INF + " tt ");
		sqlBuilder.append(" start with tt.ins_uuid='" + ins_uuid + "'");
		sqlBuilder.append(" connect by prior tt.ins_uuid = tt.sup_ins_uuid)");
		sql = sql + sqlBuilder.toString();
		return sql;
	}

	/**
	 * 获取会员权限拼装sql
	 * 
	 * @param request
	 * @param sq1
	 * @param prefix
	 * @return
	 */
	public String getCustomAuthSql(HttpServletRequest request, String sql, String prefix, Map map) {
		if (!StringUtils.isBlank(prefix)) {
			prefix = prefix + ".";
		} else {
			prefix = "";
		}
		String and = "and";
		if (map != null) {
			and = StringUtils.objToString(String.valueOf(map.get("and")));
		}
		StringBuilder sqlBuilder = new StringBuilder();
		// 获取登陆用户名

		String ins_phone = String.valueOf(request.getSession().getAttribute(SystemConstant.SESSION_LOGIN_INS_PHONE));
		// 如果是机构信息，则查询机构表
		// 如果此用户是属于某个机构，则获取当前机构及下级机构代码
		sqlBuilder.append(" " + and + " " + prefix + "custom_no in ");
		sqlBuilder.append(" (select tt.custom_no from " + Table.T_APP_CUSTOM_INF + " tt ");
		sqlBuilder.append(" start with tt.login_id='" + ins_phone + "'");
		sqlBuilder.append(" connect by prior tt.custom_uuid = tt.sup_custom_uuid)");
		sql = sql + sqlBuilder.toString();
		return sql;
	}

	/**
	 * 机构会员清算交易汇总分页查询
	 * @param request
	 * @param pagination
	 * @param condition
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	public Map<String, Object> getStlMchntListForIns(HttpServletRequest request, Pagination pagination, App_Log_Stl_Mchnt_SumEntity condition, 
			String beginDate, String endDate){
		JSONObject json = getStlMchntSqlForListAndExport(request, condition, beginDate, endDate);
		pagination.setQueryString(json.getString("sql"));
		return logStlMchntSumDao.getPaginationMap(pagination, "T", json.getObject("params", List.class));
	}
	
	/**
	 * 机构会员清算交易汇总导出
	 * @param condition
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	public String exportStlMchntForIns(HttpServletRequest request, App_Log_Stl_Mchnt_SumEntity condition, String beginDate, String endDate, String fileName){
		JSONObject json = getStlMchntSqlForListAndExport(request, condition, beginDate, endDate);
		List<Map> resultList = logStlMchntSumDao.queryForList(json.getString("sql"), json.getObject("params", List.class));
		return ExcelUtil.writeExcel(fileName, "机构会员清算交易汇总", exportStlMchntSumForInsHeaders(), resultList);
	}
	
	/**
	 * 机构会员清算交易汇总查询数据汇总
	 * @param request
	 * @param condition
	 * @param beginDate
	 * @param endDate
	 * @param fileName
	 * @return
	 */
	public Map<String, Object> getStlMchntSumForIns(HttpServletRequest request, App_Log_Stl_Mchnt_SumEntity condition, String beginDate, String endDate){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT NVL(TO_CHAR(SUM(T.AMT), 'FM99999999999990.00'), '0.00') AMT,")
		.append("       NVL(TO_CHAR(SUM(T.MIDFEE), 'FM99999999999990.00'), '0.00') MIDFEE,")
		.append("       NVL(TO_CHAR(SUM(T.CUSTOM_STL_FEE), 'FM99999999999990.00'), '0.00') CUSTOM_STL_FEE,")
		.append("       NVL(TO_CHAR(SUM(T.INS_STL_FEE), 'FM99999999999990.00'), '0.00') INS_STL_FEE,")
		.append("       NVL(TO_CHAR(SUM(T.CUSTOM_STL_FEE - T.INS_STL_FEE), 'FM99999999999990.00'), '0.00') PROFIT_FEE");
		JSONObject json = getStlMchntSqlAndParams(condition, beginDate, endDate);
		sql.append(json.getString("sql"));
		List<Map<String, Object>> list = logStlMchntSumDao.queryForList(getCustomAuthSql(request, sql.toString(), "T", null), json.getObject("params", List.class));
		return list != null && !list.isEmpty() ? list.get(0) : new HashMap<>();
	}
	
	/**
	 * 查询列表和导出数据的sql
	 * @param condition
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	private JSONObject getStlMchntSqlForListAndExport(HttpServletRequest request, App_Log_Stl_Mchnt_SumEntity condition, String beginDate, String endDate){
		StringBuffer sql = new StringBuffer();
		/*查询出的字段*/
		sql.append("SELECT NULL ID,")
		.append("       T.STL_DT,")
		.append("       T.CUSTOM_NO,")
		.append("       RPAD(SUBSTR(T.LOGIN_ID, 1, 3), LENGTH(T.LOGIN_ID) - 4, '*') ||")
		.append("       SUBSTR(T.LOGIN_ID, LENGTH(T.LOGIN_ID) - 3, 4) || '-' || T.NAME CUSTOM_NAME,")
		.append("       T.INS_CD || '-' || T.INS_CN_NM INS_NAME,")
		.append("       T.SERVICE_NO || '-' || T.SERVICE_NAME SERVICE_NAME,")
		.append("       T.PARAM_NO || '-' || T.PARAM_NAME PARAM_NAME,")
		.append("       SUM(T.CNT) CNT,")
		.append("       TO_CHAR(SUM(T.AMT), 'FM99999999999990.00') AMT,")
		.append("       TO_CHAR(SUM(T.MIDFEE), 'FM99999999999990.00') MIDFEE,")
		.append("       TO_CHAR(SUM(T.CUSTOM_STL_FEE), 'FM99999999999990.00') CUSTOM_STL_FEE,")
		.append("       TO_CHAR(SUM(T.INS_STL_FEE), 'FM99999999999990.00') INS_STL_FEE,")
		.append("       TO_CHAR(SUM(T.CUSTOM_STL_FEE - T.INS_STL_FEE), 'FM99999999999990.00') PROFIT_FEE");
		/*查询条件和参数*/
		JSONObject json = getStlMchntSqlAndParams(condition, beginDate, endDate);
		sql.append(json.getString("sql"));
		sql = new StringBuffer(getCustomAuthSql(request, sql.toString(), "T", null))
		.append(" GROUP BY T.STL_DT,")
		.append("          T.CUSTOM_NO,")
		.append("          T.LOGIN_ID,")
		.append("          T.NAME,")
		.append("          T.INS_CD,")
		.append("          T.INS_CN_NM,")
		.append("          T.SERVICE_NO,")
		.append("          T.SERVICE_NAME,")
		.append("          T.PARAM_NO,")
		.append("          T.PARAM_NAME")
		.append(" ORDER BY STL_DT DESC, AMT DESC");
		json.put("sql", sql.toString());
		
		return json;
	}
	
	
	/**
	 * 组装查询条件
	 * @param condition
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	private JSONObject getStlMchntSqlAndParams(App_Log_Stl_Mchnt_SumEntity condition, String beginDate, String endDate){
		StringBuffer sql = new StringBuffer();
		List<String> params = new ArrayList<>();
		sql.append("  FROM T_APP_LOG_STL_MCHNT_SUM T")
		.append(" WHERE 1 = 1");
		/*机构号*/
		if(StringUtils.isNotBlank(condition.getIns_cd())){
			sql.append("   AND T.INS_CD LIKE ?");
			params.add("%" + condition.getIns_cd() + "%");
		}
		/*会员编号*/
		if(StringUtils.isNotBlank(condition.getCustom_no())){
			sql.append("   AND T.CUSTOM_NO LIKE ?");
			params.add("%" + condition.getCustom_no() + "%");
		}
		/*会员名称*/
		if(StringUtils.isNotBlank(condition.getName())){
			sql.append("   AND T.NAME LIKE ?");
			params.add("%" + condition.getName() + "%");
		}
		/*手机号(登录账号)*/
		if(StringUtils.isNotBlank(condition.getLogin_id())){
			sql.append("   AND T.LOGIN_ID LIKE ?");
			params.add("%" + condition.getLogin_id() + "%");
		}
		sql.append("   AND T.STL_DT <= ?");
		sql.append("   AND T.STL_DT >= ?");
		/*清算日期*/
		if(StringUtils.isBlank(endDate)){
			endDate = DateUtil.getPreDate();
		}
		if(StringUtils.isBlank(beginDate)){
			beginDate = DateUtil.getPreDate();
		}
		params.add(endDate.replaceAll("-", ""));
		params.add(beginDate.replaceAll("-", ""));
		
		JSONObject json = new JSONObject();
		json.put("sql", sql.toString());
		json.put("params", params);
		return json;
	}
	
	private List<String> exportStlMchntSumForInsHeaders(){
		List<String> headers = new ArrayList<>();
		headers.add("清算日期");
		headers.add("会员编号");
		headers.add("会员名称");
		headers.add("所属机构");
		headers.add("业务名称");
		headers.add("业务参数名称");
		headers.add("交易笔数(笔)");
		headers.add("交易金额");
		headers.add("会员手续费");
		headers.add("会员结算手续费");
		headers.add("机构结算手续费");
		headers.add("交易分润毛利");
		return headers;
	}
}
